package name.williamwall.buxoid;
/**
 * This software is made available under the Gnu General Public License v3
 * and has absolutely no waranty. You may change the software to suit your needs.
 * 
 * Author: William Wall, IV
 */
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Currency;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class BuxoidDb {
	private static final int DATABASE_VERSION = 6;
	private static final String DATABASE_NAME = "buxoid.db";
	
	// Table constants
	public static final String TABLE_ACCOUNT = "account";
	public static final String TABLE_ANALYSIS = "analysis";
	public static final String TABLE_TRANSACTION = "txn";
	public static final String TABLE_TAG = "tag";
	public static final String TABLE_IMPACT = "impact";
	public static final String TABLE_SETTING = "setting";
	

	// TABLE_SETTING field contstants
	public static final String SETTING_ROWID = "_id";
	public static final String SETTING_KEY = "key";
	public static final String SETTING_VALUE = "value";
	
	// setting constants
	public static final String SETTING_USER = "user";
	public static final String SETTING_PASS = "pass";
	public static final String SETTING_BUXOID_PASS = "buxoidPass";
	public static final String SETTING_KEEP_DATA = "keepData";
	public static final String SETTING_CURRENCY_CODE = "currencyCode";
	public static final String SETTING_BALANCE_TYPE  = "balanceType";
	public static final String SETTING_SCHEDULE_UPDATE = "scheduleUpdate";
	public static final String SETTING_UPDATE_HOUR = "updateHour";
	public static final String SETTING_UPDATE_MINUTE = "updateMinute";
	public static final String SETTING_NOTIFY_BALANCE = "notifyBalance";
	public static final String SETTING_NOTIFY_BUDGET = "notifyBudget";
	public static final String SETTING_SYNC_MESSAGE = "syncMessage";
	
	// TABLE_ACCOUNT field constants
	public static final String ACCOUNT_ROWID = "_id";
	public static final String ACCOUNT_BUXID = "buxid";
	public static final String ACCOUNT_NAME = "name";
	public static final String ACCOUNT_BANK = "bank";
	public static final String ACCOUNT_LAST_SYNC = "lastSync";
	public static final String ACCOUNT_LAST_BUX_SYNC = "lastBuxSync";
	public static final String ACCOUNT_BALANCE = "balance";
	
	// TABLE_TRANS field constants
	public static final String TRANS_ROWID = "_id";
	public static final String TRANS_BUXID = "buxid";
	public static final String TRANS_ACCOUNTID = "aid";
	public static final String TRANS_DATE = "date";
	public static final String TRANS_DESC = "desc";
	public static final String TRANS_AMOUNT = "amount";
	public static final String TRANS_TYPE = "type";
	public static final String TRANS_TAGS = "tags";
	public static final String TRANS_NOTES = "notes";
	public static final String TRANS_STATUS = "status";
	public static final String TRANS_UPLOAD = "upload";
	
	public static final int TRANS_STATUS_PENDING = 1;
	public static final int TRANS_STATUS_CLEAR = 0;
	
	public static final int TRANS_DONT_UPLOAD = 0;
	public static final int TRANS_DO_UPLOAD = 1;
	public static final int TRANS_UPLOADED = 2;
	
	// TABLE_TAG field constants
	public static final String TAG_ROWID = "_id";
	public static final String TAG_BUXID = "buxid";
	public static final String TAG_NAME = "name";
	public static final String TAG_PARENT = "parent";
	public static final String TAG_LIMIT = "budget";
	
	// TABLE_IMPACT field constants
	public static final String IMPACT_ROWID = "_id";
	public static final String IMPACT_ENTITY = "entity";
	public static final String IMPACT_TYPE = "type";
	public static final String IMPACT_BALANCE = "balance";
	public static final String IMPACT_DESC = "desc";
	
	// TABLE_ANALYSIS field constants
	public static final String ANALYSIS_ROWID = "_id";
	public static final String ANALYSIS_YEAR = "year";
	public static final String ANALYSIS_MONTH = "month";
	public static final String ANALYSIS_TAGID = "tagid";
	public static final String ANALYSIS_TAG = "tag";
	public static final String ANALYSIS_COLOR = "color";
	public static final String ANALYSIS_AMOUNT = "amount";
	
	// create table sql statement
	private static final String CREATE_SETTING =
		"create table " + TABLE_SETTING + " ( "
			+ SETTING_ROWID + " integer primary key autoincrement, "
			+ SETTING_KEY + " text, "
			+ SETTING_VALUE + " text);";
	
	private static final String CREATE_ACCOUNT = 
		"create table " + TABLE_ACCOUNT + " ( "
		 	+ ACCOUNT_ROWID + " integer primary key, "
		 	+ ACCOUNT_BUXID + " text, "
			+ ACCOUNT_NAME + " text, "
			+ ACCOUNT_BANK + " text, "
			+ ACCOUNT_LAST_SYNC + " text, "
			+ ACCOUNT_LAST_BUX_SYNC + " text, "
			+ ACCOUNT_BALANCE + " real);";

	private static final String CREATE_TRANSACTION =
		"create table " + TABLE_TRANSACTION + " ( "
			+ TRANS_ROWID + " integer primary key autoincrement, "
			+ TRANS_BUXID + " text, "
			+ TRANS_ACCOUNTID + " text, "
			+ TRANS_DATE + " text, "
			+ TRANS_DESC + " text, "
			+ TRANS_AMOUNT + " real, "
			+ TRANS_TYPE + " text, "
			+ TRANS_TAGS + " text, "
			+ TRANS_NOTES + " text, "
			+ TRANS_STATUS + " integer, "
			+ TRANS_UPLOAD + " integer);";
	
	private static final String CREATE_TAG = 
		"create table " + TABLE_TAG + " ( "
			+ TAG_ROWID + " integer primary key autoincrement, "
			+ TAG_BUXID + " text, "
			+ TAG_NAME + " text, "
			+ TAG_PARENT + " text," 
			+ TAG_LIMIT + " real);";
	
	private static final String CREATE_IMPACT = 
		"create table " + TABLE_IMPACT + " ( "
			+ IMPACT_ROWID + " integer primary key, "
			+ IMPACT_ENTITY + " text, "
			+ IMPACT_TYPE + " text, "
			+ IMPACT_BALANCE + " real, "
			+ IMPACT_DESC + " text);";
	
	private static final String CREATE_ANALYSIS = 
		"create table " + TABLE_ANALYSIS + " ( "
			+ ANALYSIS_ROWID + " integer primary key, "
			+ ANALYSIS_YEAR + " integer, "
			+ ANALYSIS_MONTH + " integer, "
			+ ANALYSIS_TAGID + " text, "
			+ ANALYSIS_TAG + " text, "
			+ ANALYSIS_COLOR + " text, "
			+ ANALYSIS_AMOUNT + " real);";
	
	private SQLiteDatabase mDb;
	private final Context mCtx;
	private SimpleDateFormat mSyncFormat, mReadFormat; 
	public SimpleDateFormat mWriteFormat;
	
	private static final String[] MONTHS = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
	
	
	private String[] mDefaultValues = new String [] {
			"",		// buxfer user
			"", 	// buxfer password
			"",		// buxoid password
			"0",	// default to keep transactions forever
			"0",	// ledger balance 0 / available balance 1
			"0",	// schedule updates
			"8",	// scheduled update hour
			"0",	// scheduled update minute
			"0",	// notify balance
			"0",	   // notify over budget
			"150" // currency code - defaults to USD      
	};
	
	/**
	 * Constructor sets the context so that the db can be opened/created
	 * @param ctx The context within which to work.
	 */
	public BuxoidDb(Context context) {
		this.mCtx = context;
		mSyncFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		mReadFormat = new SimpleDateFormat("d MMM yy");
		mWriteFormat = new SimpleDateFormat("yyyy-MM-dd");
	}
	
	public Context getContext() {
		return mCtx;
	}
	
	public Currency getCurrency() {
	    int index = 150;
	    index = Integer.parseInt(getSetting(SETTING_CURRENCY_CODE));
	    return Currency.getInstance(
	            (String) mCtx.getResources().getTextArray(R.array.currency_codes)[index]
	    );
	}
	
	/**
	 * Tries to open the database. If it does not exist, then the function attempts
	 * to create the database.
	 * @return this (self-reference)
	 * @throws SQLException if the DB could not be opened or created.
	 */
	public BuxoidDb open () throws SQLException {

		mDb = mCtx.openOrCreateDatabase(DATABASE_NAME, 0, null);
		
		if (mDb.getVersion() != DATABASE_VERSION) {
		    if (mDb.getVersion() == 5) {
		        // add a couple of settings for automatic updates
	            ContentValues cv = new ContentValues();
	            
	            cv.put(SETTING_KEY, SETTING_CURRENCY_CODE);
	            cv.put(SETTING_VALUE, "150");
	            mDb.insert(TABLE_SETTING, null, cv);
		    } else {
    			mCtx.deleteDatabase(DATABASE_NAME);
    			mDb = mCtx.openOrCreateDatabase(DATABASE_NAME, 0, null);
    			
    			// create tables
    			mDb.execSQL(CREATE_SETTING);
    			createDefaultSettings();
    			mDb.execSQL(CREATE_ACCOUNT);
    			mDb.execSQL(CREATE_TRANSACTION);
    			mDb.execSQL(CREATE_TAG);
    			mDb.execSQL(CREATE_IMPACT);
    			mDb.execSQL(CREATE_ANALYSIS);
    			
    			// set database version
    			mDb.setVersion(DATABASE_VERSION);
		    }
		}
		return this;
	}
	
	public void close() {
		mDb.close();
	}
	
	public void createDefaultSettings() {
		ContentValues cv = new ContentValues();
		
		String[] defaultKeys = new String [] {
				SETTING_USER,
				SETTING_PASS,
				SETTING_BUXOID_PASS,
				SETTING_KEEP_DATA,
				SETTING_BALANCE_TYPE,
				SETTING_SCHEDULE_UPDATE,
				SETTING_UPDATE_HOUR,
				SETTING_UPDATE_MINUTE,
				SETTING_NOTIFY_BALANCE,
				SETTING_NOTIFY_BUDGET,
				SETTING_CURRENCY_CODE
		};
		
		for (int i = 0; i < defaultKeys.length; i++) {
			cv.clear();
			cv.put(SETTING_KEY, defaultKeys[i]);
			cv.put(SETTING_VALUE, mDefaultValues[i]);
			mDb.insert(TABLE_SETTING, null, cv);
		}
	}
	
	private String checkDate(String date) {
		// if the date has not been formatted correctly, then format it
	    date = date.trim();
		if (date.indexOf("-") == -1) {
			if (date.equalsIgnoreCase("Today")) {
				date = mWriteFormat.format(new Date());
			} else {
				if (date.length() <= 6) {
					// tack on current year
					date += " " + new SimpleDateFormat("yy").format(new Date());
				}
				
				// reformat date string into something that can be used in an order by clause
				try {
				    date = mWriteFormat.format(mReadFormat.parse(date));
				} catch (ParseException e) {
				    // do it ourselves
			        try {
			            String[] parts = date.split("\\s");
			            
			            // day
			            int d = Integer.parseInt(parts[0]);
			            parts[0] = (d < 10 ? "0": "") + d;
			            
			            // month
			            for (int i = 0; i < MONTHS.length; i++) {
			                if (MONTHS[i].equalsIgnoreCase(parts[1])) {
			                    i++;
			                    parts[1] = (i < 10 ? "0": "") + i;
			                }
			            }
			            
			            // year
			            int cy = Calendar.getInstance().get(Calendar.YEAR);
			            int y = Integer.parseInt(parts[2]) + (cy / 100) * 100;
			            
			            if (y - cy > 50) {
			                y -= 100;
			            }
			            
			            date = y + "-" + parts[1] + "-" + parts[0];
			        } catch (Exception e1) {
			            // damn...
			            date = "2009-01-01-error";
			        }
				}
			}
		}
		return date;
	}
	
	public long createAccount(int id, String buxid, String name, String bank, Double balance, String lastSync) {
		ContentValues values = new ContentValues();
		values.put(ACCOUNT_ROWID, id);
		values.put(ACCOUNT_BUXID, buxid);
		values.put(ACCOUNT_NAME, name);
		values.put(ACCOUNT_BANK, bank);
		values.put(ACCOUNT_BALANCE, balance);
		values.put(ACCOUNT_LAST_SYNC, (lastSync == null ? "never": lastSync));
		values.put(ACCOUNT_LAST_BUX_SYNC, mSyncFormat.format(System.currentTimeMillis()));
		return mDb.insert(TABLE_ACCOUNT, null, values);
	}
	
	public long createAnalysis(int month, int year, String tagid, String tag, String color, Double amount) {
		ContentValues values = new ContentValues();
		values.put(ANALYSIS_YEAR, year);
		values.put(ANALYSIS_MONTH, month);
		values.put(ANALYSIS_TAGID, tagid);
		values.put(ANALYSIS_TAG, tag);
		values.put(ANALYSIS_COLOR, color);
		values.put(ANALYSIS_AMOUNT, amount);
		return mDb.insert(TABLE_ANALYSIS, null, values);
	}
	
	/**
	 * Creates the "Other" section of the legend by summing up all tags that aren't in
	 * the top 9 for the current month.
	 * @param month in 1-12 format
	 * @param year in yyyy format
	 * @return
	 */
	public long createAnalysisOther(int month, int year) {
		String[] colors = new String [] {
				"3C5CE9", // blue
				"2E722C", // green
				"962426", // red
				"FABC4C", // yellow
				"90B7DE", // light blue
				"A2C488", // light green
				"F35484", // pink
				"13F0F0", // cyan
				"DDDDDD", // light gray
				"777777", // dark gray
		};
		
		Cursor c = fetchTop9Analysis(month, year);
		String ids = "";
		ContentValues values = new ContentValues();
		int i;
		
		// get ids and update colors for analysis entries pulled
		if (c.getCount() > 0 && c.moveToFirst()) {
			do {
				i = c.getPosition();
				ids += i == 0 ? c.getString(0): "," + c.getString(0);
				values.put(ANALYSIS_COLOR, colors[i]);
				mDb.update(TABLE_ANALYSIS, values, ANALYSIS_ROWID + "=" + c.getInt(0), null);
			} while (c.moveToNext());
		}
		c.close();
		
		double amount = getAnalysisOtherSum(month, year, ids);
		if (amount != 0) {
			// create "Other" entry to display in the analysis view
			return createAnalysis(month, year, "", "Other", colors[9], amount);
		} else {
			return -1;
		}
	}
	
	public long createImpact(int id, String entity, String type, double balance, String desc) {
		ContentValues cv = new ContentValues();
		cv.put(IMPACT_ROWID, id);
		cv.put(IMPACT_ENTITY, entity);
		cv.put(IMPACT_TYPE, type);
		cv.put(IMPACT_BALANCE, balance);
		cv.put(IMPACT_DESC, desc);
		return mDb.insert(TABLE_IMPACT, null, cv);
	}
	
	public long createTag(String buxid, String name, String parent) {
		ContentValues cv = new ContentValues();
		cv.put(TAG_BUXID, buxid);
		cv.put(TAG_NAME, name);
		cv.put(TAG_PARENT, parent);
		
		if (tagExists(buxid)) {
			return mDb.update(TABLE_TAG, cv, TAG_BUXID + "=?", new String [] {buxid});
		} else {
			return mDb.insert(TABLE_TAG, null, cv);
		}
	}
	
	public long createTransaction(String buxid, String accountId, String date, String desc, String type, String tags, String notes, String status, int upload, double amount) throws ParseException {
		ContentValues cv = new ContentValues();
		
		date = checkDate(date);
		
		cv.put(TRANS_BUXID, buxid);
		cv.put(TRANS_ACCOUNTID, accountId);
		cv.put(TRANS_DATE, date);
		cv.put(TRANS_DESC, desc);
		cv.put(TRANS_TYPE, type);
		cv.put(TRANS_TAGS, tags);
		cv.put(TRANS_NOTES, notes);
		cv.put(TRANS_STATUS, (status.toLowerCase().equals("pending") ? TRANS_STATUS_PENDING : TRANS_STATUS_CLEAR));
		cv.put(TRANS_UPLOAD, upload);
		cv.put(TRANS_AMOUNT, amount);
		
		return mDb.insert(TABLE_TRANSACTION, null, cv);
	}
	
	public void deleteAll() {
		// delete all accounts
		deleteAllAccounts();
		
		// delete all impacts
		deleteAllImpacts();
		
		// delete all transactions
		deleteAllTransactions();
		
		// delete all analysis
		deleteAllAnalysis();
		
		// I don't think budgets should be removed since they aren't synced.
		// The primary function of the "Reset Data" is to repopulate with data
		// from Buxfer.
	}
	
	public boolean deleteAllAccounts() {
		return mDb.delete(TABLE_ACCOUNT, null, null) > 0;
	}
	
	public boolean deleteAllAnalysis() {
		// remove all related files
		String [] fileList = mCtx.fileList();
		for (String filename: fileList) {
			mCtx.deleteFile(filename);
		}
		
		// delete data
		return mDb.delete(TABLE_ANALYSIS, null, null) > 0;
	}
	
	public boolean deleteAllImpacts() {
		return mDb.delete(TABLE_IMPACT, IMPACT_ROWID + " IS NOT NULL", null) > 0;
	}
	
	public boolean deleteAllTransactions() {
		// delete all transactions except pending and marked "do not upload"
		return mDb.delete(TABLE_TRANSACTION, TRANS_STATUS + "=" + TRANS_STATUS_CLEAR + " or " + TRANS_UPLOAD + "<>" + TRANS_DONT_UPLOAD, null) > 0;
	}
	
	public boolean deleteAnalysisMonth(int month, int year) {
		// delete all analysis entries for the given month
		return mDb.delete(TABLE_ANALYSIS, ANALYSIS_MONTH + "=" + month + " AND " + ANALYSIS_YEAR + "=" + year, null) > 0;
	}
	
	public boolean deleteBudget(long rowid) {
		ContentValues values = new ContentValues();
		values.put(TAG_LIMIT, (String) null);
		return mDb.update(TABLE_TAG, values, TAG_ROWID + "=" + rowid, null) > 0;
	}
	
	public void deleteOldData() {
		int numYears = Integer.parseInt(getSetting(SETTING_KEEP_DATA));
		
		if (numYears > 0) {
			Calendar cal = Calendar.getInstance();
			int month = cal.get(Calendar.MONTH) + 1;
			int year = cal.get(Calendar.YEAR) - numYears;
			
			if (month == 12) {
				month = 1;
				year++;
			} else {
				month++;
			}
			
			String date = year + "-" + (month < 10 ? "0" + month: month) + "-01";
//			Log.d("Buxfer", "deleting data older than " + date);
			
			// delete old transactions
			mDb.delete(TABLE_TRANSACTION, TRANS_DATE + " < ?", new String [] {date});
			
			// delete old analysis entries
			mDb.delete(TABLE_ANALYSIS, ANALYSIS_MONTH + "<" + month + " and " + ANALYSIS_YEAR + "<" + year, null);
			
			// delete old chart images
			String filename = "analysis" + year + month + ".png";
			while (mCtx.deleteFile(filename)) {
				// decrement the month and year
				month--;
				if (month == 0) {
					month = 12;
					year--;
				}
				// reset the filename
				filename = "analysis" + year + month + ".png";
			}
		}
	}
	
	/**
	 * Deletes at most one quick transaction that matches the given values
	 * @param accountId - the buxfer account id
	 * @param date
	 * @param amount 
	 * @return true if successful, false otherwise
	 * @throws ParseException 
	 */
	public void deleteQuickTransaction(String accountId, String date, double amount) throws ParseException {
		date = checkDate(date);

		Cursor c = mDb.query(TABLE_TRANSACTION, new String[] {TRANS_ROWID}, 
			TRANS_ACCOUNTID + "= ? AND "
			+ TRANS_STATUS + "=" + TRANS_STATUS_PENDING + " AND " 
			+ TRANS_UPLOAD + "=" + TRANS_DONT_UPLOAD + " AND " 
			+ "strftime('%s', ?) - strftime('%s', " + TRANS_DATE + ") >= 0 AND " 
			+ TRANS_AMOUNT + "=" + amount + " LIMIT 1", 
			new String [] {accountId, date}, null, null, null);
		
		if (c.getCount() > 0 && c.moveToFirst()) {
			// delete the transaction
		    long id = c.getLong(0);
		    c.close();
			deleteTransaction(id);
		}
	}
	
	
	public boolean deleteTransaction(long rowid) {
		return mDb.delete(TABLE_TRANSACTION, TRANS_ROWID + "=" + rowid, null) > 0;
	}
	
	public boolean deleteTransactions(String accountId) {
		return mDb.delete(TABLE_TRANSACTION, TRANS_ACCOUNTID + "=?", new String [] {accountId}) > 0;
	}
	
	public boolean deleteTransactions(String[] notTheseIds, String date) {
		StringBuilder sb = new StringBuilder();
		sb.append(TRANS_BUXID + " NOT IN (");
	    for (int i = 0; i < notTheseIds.length; i++) {
	        if (i > 0) {
	            sb.append(",");
	        }
	        sb.append("'");
	        sb.append(notTheseIds[i]);
	        sb.append("'");
	    }
	    sb.append(") AND ");
	    sb.append(TRANS_DATE);
	    sb.append(" >= '"); 
	    sb.append(date); 
	    sb.append("'");
	    
	    return mDb.delete(TABLE_TRANSACTION, sb.toString(), null) > 0;
	}
	
	public boolean deleteUploadedTrans() {
	    return mDb.delete(TABLE_TRANSACTION, TRANS_UPLOAD + "= 1", null) > 0;
	}
	
	public Cursor fetchAllAccounts() {
		String[] columns = new String [] {
				ACCOUNT_ROWID,
				ACCOUNT_BUXID,
				ACCOUNT_NAME,
				ACCOUNT_BALANCE,
				ACCOUNT_LAST_SYNC
		};
		
		return mDb.query(TABLE_ACCOUNT, columns, null, null, null, null, ACCOUNT_NAME);
	}
	
	/**
	 * Fetches all the budgets (tags with limits) and their amounts from the DB.
	 * @param month in 1-12 format
	 * @param year in yyyy format
	 * @return
	 */
	public Cursor fetchAllBudgets(int month, int year) {
		// Budgets are simply tags that have the limit field set.
		// The corresponding amount is in the analysis table
		String sql = "SELECT t." + TAG_ROWID 
			+ ", t." + TAG_NAME 
			+ ", t." + TAG_LIMIT
			+ ", CASE WHEN a." + ANALYSIS_AMOUNT + " IS NULL THEN 0 ELSE a." + ANALYSIS_AMOUNT
			+ " END AS " + ANALYSIS_AMOUNT
			+ " FROM " + TABLE_TAG + " t "
				+ " LEFT JOIN " + TABLE_ANALYSIS + " a "
					+ " ON t." + TAG_BUXID + " = " + "a." + ANALYSIS_TAGID
					+ " AND a." + ANALYSIS_MONTH + "=" + month
					+ " AND a." + ANALYSIS_YEAR + "=" + year
			+ " WHERE t." + TAG_LIMIT + " IS NOT NULL"
			+ " ORDER BY t." + TAG_NAME;
		
		return mDb.rawQuery(sql, null);
	}
	
	public Cursor fetchAllImpacts() {
		String [] columns = new String [] {
				IMPACT_ROWID,
				IMPACT_ENTITY,
				IMPACT_DESC,
				IMPACT_BALANCE
		};
		
		return mDb.query(TABLE_IMPACT, columns, null, null, null, null, IMPACT_ENTITY);
	}
	public Cursor fetchAllParentTags() {
		String [] columns = new String [] {
				TAG_ROWID,
				TAG_NAME,
				TAG_LIMIT
		};
		
		return mDb.query(TABLE_TAG, columns, TAG_PARENT + "= ?", new String [] {"-1"}, null, null, TAG_NAME);
	}
	public Cursor fetchAllTags() {
		String [] columns = new String [] {
				TAG_ROWID,
				TAG_NAME,
				TAG_LIMIT
		};
		
		return mDb.query(TABLE_TAG, columns, null, null, null, null, TAG_NAME);
	}
	
	public Cursor fetchAllTransactions(String accountId) {
		String [] columns = new String [] {
				TRANS_ROWID,
				TRANS_DATE,
				TRANS_DESC,
				TRANS_TYPE,
				TRANS_AMOUNT,
				TRANS_TAGS,
				TRANS_STATUS
		};
		
		return mDb.query(TABLE_TRANSACTION, columns, TRANS_ACCOUNTID + "= ?", new String [] {accountId}, null, null, TRANS_DATE + " desc");
	}
	
	public Cursor fetchAllTransactionsForMonth(String accountId, int month, int year, String tagName) {
		String [] columns = new String [] {
				TRANS_ROWID,
				TRANS_DATE,
				TRANS_DESC,
				TRANS_TYPE,
				TRANS_AMOUNT,
				TRANS_TAGS,
				TRANS_STATUS
		};
		
		String start = year + "-" + (month < 10 ? "0" + month: month) + "-01";
		String end = getNextMonthDate(month, year);
		
//		Log.d("Buxfer", "start " + start + " end " + end);
		
		String selection = TRANS_DATE + " >= ? AND " + TRANS_DATE + " < ? ";
		
		ArrayList<String> args = new ArrayList<String>();
		args.add(start);
		args.add(end);
		
		if (accountId != null) {
			selection += " AND " + TRANS_ACCOUNTID + "= ? ";
			args.add(accountId);
		} 
		
		if (tagName != null && tagName.length() > 1) {
		    Cursor c = fetchTags(TAG_NAME + " = '" + tagName + "'");
		    if (c.getCount() > 0 && c.moveToFirst()) {
		       selection += " AND (" + getTagListFromRootTag(c.getInt(0)) + ") ";
//		       Log.d("Buxfer", selection);
		    }
		}
		
		String [] selectionArgs = new String [args.size()];
		
		return mDb.query(TABLE_TRANSACTION, columns, selection, args.toArray(selectionArgs), null, null, TRANS_DATE + " desc");
	}
	
	public Cursor fetchAccount(long rowid) {
		String[] columns = new String [] {
				ACCOUNT_ROWID,
				ACCOUNT_BUXID,
				ACCOUNT_NAME,
				ACCOUNT_BALANCE,
				ACCOUNT_LAST_SYNC,
				ACCOUNT_LAST_BUX_SYNC
		};
		
		return mDb.query(TABLE_ACCOUNT, columns, ACCOUNT_ROWID + "=" + rowid, null, null, null, null);
	}
	
	public Cursor fetchAccount(String buxid) {
		String[] columns = new String [] {
				ACCOUNT_ROWID,
				ACCOUNT_BUXID,
				ACCOUNT_NAME,
				ACCOUNT_BALANCE,
				ACCOUNT_LAST_SYNC,
				ACCOUNT_LAST_BUX_SYNC
		};
		
		return mDb.query(TABLE_ACCOUNT, columns, ACCOUNT_BUXID + "= ?", new String [] {buxid}, null, null, null);
	}
	
	public Cursor fetchManualTransactions() {
		String sql = "SELECT "
			+ "a." + ACCOUNT_NAME + ", "
			+ "t." + TRANS_DATE + ", "
			+ "t." + TRANS_DESC + ", "
			+ "t." + TRANS_TYPE + ", "
			+ "t." + TRANS_TAGS + ", "
			+ "t." + TRANS_STATUS + ", "
			+ "t." + TRANS_AMOUNT + ", "
			+ "t." + TRANS_NOTES + " FROM "
			+ TABLE_ACCOUNT + " a INNER JOIN " + TABLE_TRANSACTION + " t ON "
			+ "a." + ACCOUNT_BUXID + " = t." + TRANS_ACCOUNTID + " WHERE "
			+ "t." + TRANS_UPLOAD + "=1";
		
		return mDb.rawQuery(sql, null);
	}
	
	public Cursor fetchAnalysis(int month, int year) {
		String[] columns = new String [] {
				ANALYSIS_ROWID,
				ANALYSIS_TAGID,
				ANALYSIS_TAG,
				ANALYSIS_COLOR,
				ANALYSIS_AMOUNT
		};
		
		return mDb.query(TABLE_ANALYSIS, columns, ANALYSIS_MONTH + "=" + month + " AND " + ANALYSIS_YEAR + "=" + year, null, null, null, ANALYSIS_TAG);
	}
	
	public Cursor fetchAnalysis(long rowid) {
		String[] columns = new String [] {
				ANALYSIS_ROWID,
				ANALYSIS_TAGID,
				ANALYSIS_TAG,
				ANALYSIS_COLOR,
				ANALYSIS_AMOUNT
		};
		
		return mDb.query(TABLE_ANALYSIS, columns, ANALYSIS_ROWID + "=" + rowid, null, null, null, ANALYSIS_TAG);
	}
	
	/**
	 * fetches top 9 analysis entries 
	 * @param month in 1-12 format
	 * @param year in yyyy format
	 * @return
	 */
	private Cursor fetchTop9Analysis(int month, int year) {
		String sql = "SELECT " + ANALYSIS_ROWID + " FROM " + TABLE_ANALYSIS
			+ " WHERE " + ANALYSIS_MONTH + "=" + month 
			+ " AND " + ANALYSIS_YEAR + "=" + year 
			+ " ORDER BY " + ANALYSIS_AMOUNT + " desc LIMIT 9";
		
		return mDb.rawQuery(sql, null);
	}
	
	/**
	 * Fetches all analysis entries marked for display in the legend
	 * @param month in 1-12 format
	 * @param year in yyyy format
	 * @return
	 */
	public Cursor fetchAnalysisLegend(int month, int year) {
		String[] columns = new String [] {
				ANALYSIS_ROWID,
				ANALYSIS_TAGID,
				ANALYSIS_TAG,
				ANALYSIS_COLOR,
				ANALYSIS_AMOUNT
		};
		
		return mDb.query(TABLE_ANALYSIS, columns, ANALYSIS_MONTH + "=" + month + " AND " + ANALYSIS_YEAR + "=" + year + " AND " + ANALYSIS_COLOR + " != ?", new String [] {""}, null, null, ANALYSIS_AMOUNT + " desc");
	}
	
	public Cursor fetchChildTags(String parentBuxId) {
		return mDb.query(TABLE_TAG, new String [] {TAG_ROWID, TAG_BUXID, TAG_NAME}, TAG_PARENT + "= ?", new String[] {parentBuxId}, null, null, null);
	}
	
	public Cursor fetchImpacts(String constraint) {
		String [] columns = new String [] {
				IMPACT_ROWID,
				IMPACT_ENTITY,
				IMPACT_DESC,
				IMPACT_BALANCE
		};
		
		return mDb.query(TABLE_IMPACT, columns, constraint, null, null, null, IMPACT_ENTITY);
	}
	
	public Cursor fetchTag(long rowid) {
		String[] columns = new String [] {
				TAG_ROWID,
				TAG_BUXID,
				TAG_NAME,
				TAG_PARENT,
				TAG_LIMIT
		};
		
		return mDb.query(TABLE_TAG, columns, TAG_ROWID + "=" + rowid, null, null, null, null);
	}
	
	public Cursor fetchTag(String buxid) {
		String[] columns = new String [] {
				TAG_ROWID,
				TAG_BUXID,
				TAG_NAME,
				TAG_PARENT,
				TAG_LIMIT
		};
		
		return mDb.query(TABLE_TAG, columns, TAG_BUXID + "=?", new String [] {buxid}, null, null, null);
	}
	
	public Cursor fetchTags(String constraint) {
		String[] columns = new String [] {
				TAG_ROWID,
				TAG_BUXID,
				TAG_NAME,
				TAG_PARENT,
				TAG_LIMIT
		};
		
		return mDb.query(TABLE_TAG, columns, constraint, null, null, null, TAG_NAME);
	}
	
	public Cursor fetchTransaction(long rowid) {
		String[] columns = new String [] {
				TRANS_ROWID,
				TRANS_BUXID,
				TRANS_ACCOUNTID,
				TRANS_DATE,
				TRANS_DESC,
				TRANS_AMOUNT,
				TRANS_TYPE,
				TRANS_TAGS,
				TRANS_NOTES,
				TRANS_STATUS,
				TRANS_UPLOAD
		};
		
		return mDb.query(TABLE_TRANSACTION, columns, TRANS_ROWID + "=" + rowid, null, null, null, null);
	}
	
	public Cursor fetchTransaction(String buxid) {
		String[] columns = new String [] {
				TRANS_ROWID,
				TRANS_BUXID,
				TRANS_ACCOUNTID,
				TRANS_DATE,
				TRANS_DESC,
				TRANS_AMOUNT,
				TRANS_TYPE,
				TRANS_TAGS,
				TRANS_NOTES,
				TRANS_STATUS,
				TRANS_UPLOAD
		};
		
		return mDb.query(TABLE_TRANSACTION, columns, TRANS_BUXID + "=?", new String [] {buxid}, null, null, null);
	}
	
	public String getAccountBuxidFromId(long rowid) {
		Cursor c = fetchAccount(rowid);
		if (c.getCount() > 0 && c.moveToFirst()) {
		    String str = c.getString(c.getColumnIndex(ACCOUNT_BUXID));
		    c.close();
			return str;
		} else {
		    c.close();
			return null;
		}
	}
	
	public int getAccountIdFromBuxid(String buxid) {
		Cursor c = fetchAccount(buxid);
		if (c.getCount() > 0 && c.moveToFirst()) {
		    int i = c.getInt(c.getColumnIndex(ACCOUNT_ROWID));
		    c.close();
			return i;
		} else {
		    c.close();
			return 0;
		}
	}
	
	public int getAnalysisCount() {
		String sql = " SELECT COUNT(" + ANALYSIS_ROWID + ") FROM " + TABLE_ANALYSIS;
		Cursor c = mDb.rawQuery(sql, null);
		if (c.getCount() > 0 && c.moveToFirst()) {
		    int i = c.getInt(0);
		    c.close();
			return i;
		} else {
		    c.close();
			return 0;
		}
	}
	
	public double getBudgetLimit(String tagId) {
		Cursor c = fetchTag(tagId);
		if (c.getCount() > 0 && c.moveToFirst()) {
		    double d = c.getDouble(c.getColumnIndex(TAG_LIMIT));
		    c.close();
			return d;
		} else {
		    c.close();
			return 0;
		}
	}
	
	public double getMonthAmountForTag(int month, int year, int tagId) {
//		Log.d("Buxfer", "getMonthAmountForTag " + month + " " + year + " " + tagId);
		String conditions = getTagListFromRootTag(tagId);
		
		String start = year + "-" + (month < 10 ? "0": "") + month + "-01";
		String end = getNextMonthDate(month, year);
		String sql = " SELECT SUM(" + TRANS_AMOUNT + ") FROM " + TABLE_TRANSACTION
			+ " WHERE " + TRANS_TYPE + " IN ('expense') "
			+ (conditions != null ? " AND (" + conditions + ") " : "")
			+ " AND " + TRANS_DATE + ">= ?"
			+ " AND " + TRANS_DATE + "< ?";	
		
		Cursor c = mDb.rawQuery(sql, new String [] {start, end});
		if (c.getCount() > 0 && c.moveToFirst()) {
		    double d = c.getDouble(0);
		    c.close();
			return d;
		} else {
		    c.close();
			return -1;
		}
	}
	
	public String getOldestValidDate() {
		int numYears = Integer.parseInt(getSetting(SETTING_KEEP_DATA));
		
		if (numYears > 0) {
			Calendar cal = Calendar.getInstance();
			int month = cal.get(Calendar.MONTH) + 1;
			int year = cal.get(Calendar.YEAR) - numYears;
			
			if (month == 12) {
				month = 1;
				year++;
			} else {
				month++;
			}
			
			return year + "-" + (month < 10 ? "0" + month: month) + "-01";
		} else {
			return "1900-01-01";
		}
	}
	
	public String getOldestTransDate() {
		String sql = " SELECT " + TRANS_DATE + " FROM " + TABLE_TRANSACTION
			+ " WHERE " + TRANS_UPLOAD + "= 2 ORDER BY " + TRANS_DATE + " asc LIMIT 1";
		
		Cursor c = mDb.rawQuery(sql, null);
		if (c.getCount() > 0 && c.moveToFirst()) {
		    String date = c.getString(0);
		    c.close();
			return date;
		} else {
		    c.close();
			return null;
		}
	}
	
	/**
	 * Gets the sum of all analysis entries matching the given month and id string.
	 * @param month in 1-12 format
	 * @param year in yyyy format
	 * @param ids a comma-separated list of row ids not to sum
	 * @return the sum
	 */
	private double getAnalysisOtherSum(int month, int year, String ids) {
		String sql = "SELECT SUM(" + ANALYSIS_AMOUNT + ") FROM " + TABLE_ANALYSIS
			+ " WHERE " + ANALYSIS_MONTH + "=" + month
			+ " AND " + ANALYSIS_YEAR + "=" + year
			+ " AND " + ANALYSIS_ROWID + " NOT IN (" + ids + ")";
		
		Cursor c = mDb.rawQuery(sql, null);
		if (c.getCount() > 0 && c.moveToFirst()) {
		    double d = c.getDouble(0);
		    c.close();
			return d;
		} else {
		    c.close();
			return 0;
		}
	}
	
	public double getPendingBalance(String accountId) {
		String sql = "SELECT SUM(CASE " + TRANS_TYPE + " WHEN 'expense' THEN " + TRANS_AMOUNT + " * -1 ELSE " + TRANS_AMOUNT + " END) as amount "
			+ "FROM " + TABLE_TRANSACTION + " WHERE " + TRANS_ACCOUNTID + " = '" + accountId 
			+ "' AND (" + TRANS_STATUS + "=" + TRANS_STATUS_PENDING
			        + " OR " + TRANS_UPLOAD + "=" + TRANS_DONT_UPLOAD + ")";
		
		Cursor c = mDb.rawQuery(sql, null);
		
		if (c.getCount() > 0 && c.moveToFirst()) {
		    double d = c.getDouble(0);
		    c.close();
			return d;
		} else {
		    c.close();
			return 0;
		}
	}
	
	public String getSetting(String key) {
		Cursor c = mDb.query(TABLE_SETTING, new String [] {SETTING_VALUE}, SETTING_KEY + "= ?", new String[] {key}, null, null, null);
		if (c.getCount() > 0 && c.moveToFirst()) {
		    String str = c.getString(0);
		    c.close();
			return str;
		} else {
		    c.close();
			return null;
		}
	}
	
	private String getTagListFromRootTag(int rootTagId) {
		Cursor c = fetchTag(rootTagId);
		if (c.getCount() > 0 && c.moveToFirst()) {
			return getTagListFromRootTag(c.getString(c.getColumnIndexOrThrow(TAG_NAME)), c.getString(c.getColumnIndexOrThrow(TAG_BUXID)));
		} else {
			return null;
		}
	}
	
	private String getTagListFromRootTag(String rootTagName, String rootTagBuxId) {
		ArrayList<String> tagList = new ArrayList<String>();
		tagList.add(rootTagBuxId);
		StringBuilder sb = new StringBuilder();
		sb.append(TRANS_TAGS + " LIKE '%" + rootTagName + "%' OR ");
		
		for (int i = 0; i < tagList.size(); i++) {
		    String id = tagList.get(i);
			Cursor c = fetchChildTags(id);
			if (c.getCount() > 0 && c.moveToFirst()) {
				do {
					tagList.add(c.getString(c.getColumnIndexOrThrow(TAG_BUXID)));
					sb.append(TRANS_TAGS + " LIKE '%" + c.getString(c.getColumnIndexOrThrow(TAG_NAME)) +"%' OR " );
				} while (c.moveToNext());
			}
		}
		String str = sb.toString();
//		Log.d("Buxfer", str);
		if (str.length() > 4) {
			return str.substring(0, str.length() - 4);
		} else {
			return null;
		}
	}
	
	public int getTransactionCount() {
		String sql = " SELECT COUNT(_id) FROM " + TABLE_TRANSACTION;
		Cursor c = mDb.rawQuery(sql, null);
		if (c.getCount() > 0 && c.moveToFirst()) {
		    int i = c.getInt(0);
		    c.close();
			return i;
		} else {
		    c.close();
			return -1;
		}
	}
	
	/**
	 * Looks for a matching transaction in the database
	 * 
	 * @param buxid
	 * @param accountId
	 * @param date
	 * @param desc
	 * @param type
	 * @param tags
	 * @param notes
	 * @param status
	 * @param upload
	 * @param amount
	 * @return true if a matching transaction exists in the DB, false otherwise 
	 */
	public boolean matchTransaction(String buxid, String accountId, String date, String desc, String type, String tags, String notes, String status, int upload, double amount) {
		date = checkDate(date);
		
		Cursor c = mDb.query(TABLE_TRANSACTION, new String [] {TRANS_BUXID},
				TRANS_BUXID + "= ? AND "
				+ TRANS_ACCOUNTID + "= ? AND "
				+ TRANS_DATE + "= ? AND "
				+ TRANS_DESC + "= ? AND "
				+ TRANS_TYPE + "= ? AND "
				+ TRANS_TAGS + "= ? AND "
				+ TRANS_NOTES + "= ? AND "
				+ TRANS_STATUS + "= ? AND "
				+ TRANS_UPLOAD + "= ? AND "
				+ TRANS_AMOUNT + "= ?", 
				new String [] {
					buxid,
					accountId,
					date,
					desc,
					type,
					tags,
					notes,
					(status.toLowerCase().equals("pending") ? TRANS_STATUS_PENDING : TRANS_STATUS_CLEAR) + "",
					upload + "",
					amount + ""
				}, null, null, null);
		
		boolean matched = c.getCount() > 0 && c.moveToFirst();
		c.close();
		return matched;
	}
	
	public boolean putSetting(String key, String value) {
		ContentValues cv = new ContentValues();
		cv.put(SETTING_VALUE, value);
		if (!settingExists(key)) {
		    return mDb.insert(TABLE_SETTING, null, cv) > 0;
		} else {
		    return mDb.update(TABLE_SETTING, cv, SETTING_KEY + "= ?", new String [] {key}) > 0;
		}
	}
	
	public boolean saveBudget(String tagName, double limit) {
		ContentValues values = new ContentValues();
		values.put(TAG_LIMIT, limit);
		return mDb.update(TABLE_TAG, values, TAG_NAME + "= ?", new String [] {tagName}) > 0;
	}
	
	public boolean settingExists(String key) {
	    Cursor c = mDb.query(TABLE_SETTING, new String [] {SETTING_KEY}, SETTING_KEY + "= ?", new String [] {key}, null, null, null);
	    boolean exists = c.getCount() > 0 && c.moveToFirst();
	    c.close();
	    return exists;
	}
	
	private boolean tagExists(String buxid) {
	    Cursor c = fetchTag(buxid);
	    boolean exists = c.getCount() > 0 && c.moveToFirst();
	    c.close();
		return exists;
	}
	
	public boolean transactionExists(String buxid) {
	    Cursor c = fetchTransaction(buxid);
	    boolean exists = c.getCount() > 0 && c.moveToFirst();
	    c.close();
		return exists;
	}
	
	/**
	 * Until API calls for editing a transaction are implemented by Buxfer.com, users
	 * may only edit manual actions until they sync them.
	 * 
	 * @param rowid the id of the transaction to check
	 * @return whether or not the user is allowed to edit the transaction
	 */
	public boolean transactionEditable(long rowid) {
		Cursor c = fetchTransaction(rowid);
		if (c.getCount() > 0 && c.moveToFirst()) {
		    boolean editable = c.getInt(c.getColumnIndexOrThrow(TRANS_UPLOAD)) != TRANS_UPLOADED;
		    c.close();
			return editable;
		} else {
		    c.close();
			return false;
		}
	}
	
	public boolean updateTransaction(long rowid, String accountId, String type, String status, String desc, String date, String tags, String notes, int upload, double amount) {
		ContentValues cv = new ContentValues();
		
		date = checkDate(date);
		
		cv.put(TRANS_ACCOUNTID, accountId);
		cv.put(TRANS_TYPE, type);
		cv.put(TRANS_STATUS, (status.toLowerCase().equals("pending") ? TRANS_STATUS_PENDING: TRANS_STATUS_CLEAR));
		cv.put(TRANS_DATE, date);
		cv.put(TRANS_DESC, desc);
		cv.put(TRANS_TAGS, tags);
		cv.put(TRANS_NOTES, notes);
		cv.put(TRANS_AMOUNT, amount);
		cv.put(TRANS_UPLOAD, upload);
		return mDb.update(TABLE_TRANSACTION, cv, TRANS_ROWID +"="+ rowid, null) > 0;
	}
	
	public boolean updateTransaction(String buxid, String accountId, String type, String status, String desc, String date, String tags, String notes, int upload, double amount) {
		ContentValues cv = new ContentValues();
		
		date = checkDate(date);
		
		cv.put(TRANS_ACCOUNTID, accountId);
		cv.put(TRANS_TYPE, type);
		cv.put(TRANS_STATUS, (status.toLowerCase().equals("pending") ? TRANS_STATUS_PENDING: TRANS_STATUS_CLEAR));
		cv.put(TRANS_DATE, date);
		cv.put(TRANS_DESC, desc);
		cv.put(TRANS_TAGS, tags);
		cv.put(TRANS_NOTES, notes);
		cv.put(TRANS_AMOUNT, amount);
		cv.put(TRANS_UPLOAD, upload);
		return mDb.update(TABLE_TRANSACTION, cv, TRANS_BUXID +"= ?", new String [] {buxid}) > 0;
	}
	
	private String getNextMonthDate(int month, int year) {
	    month++;
	    if (month == 13) {
	        month = 1;
	        year ++;
	    }
	    return year + "-" + (month < 10 ? "0": "") + month + "-01";
	}
}